﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using MyProFile_V2.Model;
using MyProFile_V2.Bll;
namespace MyProFile_V2.Data
{
    public class ColumnService
    {
        public static ArrayList getColumnById(string id)
        {
            //
            string sqlStr2 = "select c.name as colName from sysindexes i join sysindexkeys k on i.id = k.id and i.indid = k.indid join sysobjects o on i.id = o.id join syscolumns c on i.id=c.id and k.colid = c.colid where o.xtype = 'U' and c.id=@ID";
            //获得表的所有列
            string sqlStr = "select [name],xtype,colid from syscolumns where id=@ID";
            //获得主键
            string sqlStr3 = "SELECT name FROM sys.columns WHERE object_id=@ID AND is_identity=1";

            //查表中列的描述
            string sqlStrProperties = "select minor_id,value  from sys.extended_properties where major_id=@ID";

            SqlParameter[] parmas = new SqlParameter[]{
            new SqlParameter("@ID",id)
            };
            DataTable dt2 = DBHelper.QueryDataTable(sqlStr2, parmas);
            DataTable dt = DBHelper.QueryDataTable(sqlStr, parmas);
            DataTable dt3 = DBHelper.QueryDataTable(sqlStr3, parmas);
            //查表中列的描述
            DataTable dtProperties = DBHelper.QueryDataTable(sqlStrProperties, parmas);
            ArrayList al = new ArrayList();
            foreach (DataRow row in dt.Rows)
            {
                ColumnsModel cm = new ColumnsModel();
                //列名
                string tempName = row["name"].ToString();
                cm.Name = CurrentProjectFun.isCSKeyWord(tempName) ? tempName + "_" : tempName;
                //类型
                cm.Xtype = Convert.ToInt32(row["xtype"]);
                //列序
                cm.ColOrder = Convert.ToInt32(row["colid"]);
                //列的描述
                foreach (DataRow rowz in dtProperties.Rows)
                {
                    if (cm.ColOrder == Convert.ToInt32(rowz["minor_id"]))
                    {
                        cm.ColDes = Convert.ToString(rowz["value"]);
                    }
                }

                //是否为主键
                if (dt2.Rows.Count > 0)
                {
                    if (dt2.Rows[0][0].ToString().Equals(cm.Name))
                    {
                        cm.IsPK = true;
                    }
                    else
                    {
                        cm.IsPK = false;
                    }
                }
                else
                {
                    cm.IsPK = false;
                }
                //判断是不是自增列
                cm.IsIdentity = false;
                if (dt3.Rows.Count != 0)
                {
                    if (cm.Name.Equals(dt3.Rows[0][0].ToString()))
                    {
                        cm.IsIdentity = true;
                    }

                }
                //是否为空

                //描述
                //默认值

                al.Add(cm);
            }
            return al;
        }
    }
}
